# -*- coding: utf-8 -*-
import sqlite3
from sqlite3 import Error
import csv
import os

def init_data_base(db_file="new_database.db"):
    """
    初始化数据库，创建必要的表结构
    
    参数:
        db_file: 数据库文件路径
    """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        cursor = conn.cursor()
        print("数据库文件 '{}' 已准备就绪（自动创建）".format(db_file))

        # 创建原始数据表
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS raw_comments (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                商品留言 TEXT NOT NULL
            )
        """)
        
        # 清空原始数据表
        cursor.execute("DELETE FROM raw_comments")
        
        # 创建提取结果表
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS extracted_info (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                姓名 TEXT,
                电话 TEXT,
                身份证号 TEXT,
                使用日期 TEXT,
                同行人信息 TEXT,
                comment_id INTEGER,
                FOREIGN KEY (comment_id) REFERENCES raw_comments(id)
            )
        """)
        
        # 清空提取结果表
        cursor.execute("DELETE FROM extracted_info")
        
        conn.commit()
        print("已成功初始化数据库表结构")

    except Error as e:
        print("错误: {}".format(e))
        if conn:
            conn.rollback()
    finally:
        if conn:
            conn.close()

def run_sql_script(sql_file, db_file="new_database.db"):
    """
    执行SQL文件，自动创建不存在的数据库文件
    
    参数:
        sql_file: SQL脚本文件路径
        db_file: 数据库文件路径（默认自动创建new_database.db）
    """
    conn = None
    try:
        # 连接数据库，如果文件不存在则自动创建
        conn = sqlite3.connect(db_file)
        cursor = conn.cursor()
        print("数据库文件 '{}' 已准备就绪（自动创建）".format(db_file))

        # 读取并执行SQL脚本
        with open(sql_file, 'r', encoding='utf-8') as f:
            sql_script = f.read()
        
        cursor.executescript(sql_script)
        conn.commit()
        print("已成功执行SQL脚本到 '{}'".format(db_file))

    except Error as e:
        print("错误: {}".format(e))
        if conn:
            conn.rollback()
    finally:
        if conn:
            conn.close()

def import_csv_data(csv_file, db_file="new_database.db"):
    """
    从CSV文件导入数据到raw_comments表
    
    参数:
        csv_file: CSV文件路径
        db_file: 数据库文件路径
    """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        cursor = conn.cursor()
        
        # 读取CSV文件并插入数据
        with open(csv_file, 'r', encoding='utf-8') as f:
            reader = csv.DictReader(f)
            rows_inserted = 0
            for row in reader:
                if '商品留言' in row and row['商品留言'].strip():
                    cursor.execute("INSERT INTO raw_comments (商品留言) VALUES (?)", 
                                 (row['商品留言'],))
                    rows_inserted += 1
        
        conn.commit()
        print("成功从 '{}' 导入 {} 条记录".format(csv_file, rows_inserted))
        return rows_inserted
        
    except Error as e:
        print("导入错误: {}".format(e))
        if conn:
            conn.rollback()
        return 0
    finally:
        if conn:
            conn.close()

def process_data(db_file="new_database.db"):
    """
    执行数据处理，从raw_comments提取信息到extracted_info
    
    参数:
        db_file: 数据库文件路径
    """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        cursor = conn.cursor()
        
        # 清空提取结果表（避免重复数据）
        cursor.execute("DELETE FROM extracted_info")
        
        # 执行数据提取
        cursor.execute("""
            INSERT INTO extracted_info (姓名, 电话, 身份证号, 使用日期, 同行人信息, comment_id)
            SELECT
                -- 提取姓名
                TRIM(SUBSTR(商品留言, 
                       INSTR(商品留言, '姓名:') + 3,
                       CASE 
                           WHEN INSTR(商品留言, ';') > INSTR(商品留言, '姓名:') 
                           THEN INSTR(SUBSTR(商品留言, INSTR(商品留言, '姓名:') + 3), ';') - 1
                           ELSE LENGTH(商品留言) - (INSTR(商品留言, '姓名:') + 3) + 1
                       END
                      )) AS 姓名,
                
                -- 提取电话
                TRIM(SUBSTR(商品留言,
                       INSTR(商品留言, '电话:') + 3,
                       CASE
                           WHEN INSTR(SUBSTR(商品留言, INSTR(商品留言, '电话:') + 3), ';') > 0
                           THEN INSTR(SUBSTR(商品留言, INSTR(商品留言, '电话:') + 3), ';') - 1
                           ELSE LENGTH(商品留言) - (INSTR(商品留言, '电话:') + 3) + 1
                       END
                      )) AS 电话,
                
                -- 提取身份证号
                TRIM(SUBSTR(商品留言,
                       INSTR(商品留言, '身份证号:') + 5,
                       CASE
                           WHEN INSTR(SUBSTR(商品留言, INSTR(商品留言, '身份证号:') + 5), ';') > 0
                           THEN INSTR(SUBSTR(商品留言, INSTR(商品留言, '身份证号:') + 5), ';') - 1
                           ELSE LENGTH(商品留言) - (INSTR(商品留言, '身份证号:') + 5) + 1
                       END
                      )) AS 身份证号,
                
                -- 提取使用日期
                TRIM(SUBSTR(商品留言,
                       INSTR(商品留言, '使用日期:') + 5,
                       CASE
                           WHEN INSTR(SUBSTR(商品留言, INSTR(商品留言, '使用日期:') + 5), ';') > 0
                           THEN INSTR(SUBSTR(商品留言, INSTR(商品留言, '使用日期:') + 5), ';') - 1
                           ELSE LENGTH(商品留言) - (INSTR(商品留言, '使用日期:') + 5) + 1
                       END
                      )) AS 使用日期,
                
                -- 提取同行人信息
                TRIM(SUBSTR(商品留言,
                       INSTR(商品留言, '拍多份的备注他人信息:') + 12,
                       CASE
                           WHEN INSTR(SUBSTR(商品留言, INSTR(商品留言, '拍多份的备注他人信息:') + 12), ';') > 0
                           THEN INSTR(SUBSTR(商品留言, INSTR(商品留言, '拍多份的备注他人信息:') + 12), ';') - 1
                           ELSE LENGTH(商品留言) - (INSTR(商品留言, '拍多份的备注他人信息:') + 12) + 1
                       END
                      )) AS 同行人信息,
                      
                id AS comment_id
            FROM raw_comments
            WHERE 商品留言 LIKE '%姓名:%'
        """)
        
        conn.commit()
        processed_rows = cursor.rowcount
        print("成功处理 {} 条记录".format(processed_rows))
        return processed_rows
        
    except Error as e:
        print("数据处理错误: {}".format(e))
        if conn:
            conn.rollback()
        return 0
    finally:
        if conn:
            conn.close()

def export_to_csv(output_file="result.csv", db_file="new_database.db"):
    """
    将提取的所有表信息导出到CSV文件
    
    参数:
        output_file: 输出CSV文件名，默认为result.csv
        db_file: 数据库文件路径
    """
    try:
        conn = sqlite3.connect(db_file)
        cursor = conn.cursor()
        
        # 查询提取的所有数据
        cursor.execute("""
            SELECT 姓名, 电话, 身份证号, 使用日期, 同行人信息 
            FROM extracted_info 
            ORDER BY id
        """)
        
        results = cursor.fetchall()
        
        if not results:
            print("没有找到可导出的数据")
            return False
        
        # 写入CSV文件
        with open(output_file, 'w', encoding='utf-8', newline='') as csvfile:
            writer = csv.writer(csvfile)
            
            # 写入表头
            writer.writerow(['姓名', '电话', '身份证号', '使用日期', '同行人信息'])
            
            # 写入数据
            for row in results:
                writer.writerow(row)
        
        print("成功导出 {} 条记录到 '{}'".format(len(results), output_file))
        
        # 显示导出内容预览
        print("\n=== 导出内容预览 ===")
        with open(output_file, 'r', encoding='utf-8') as f:
            lines = f.readlines()
            for line in lines[:6]:  # 显示前6行（1行表头+5行数据）
                print(line.strip())
        
        return True
        
    except Exception as e:
        print("导出错误: {}".format(e))
        return False
    finally:
        if conn:
            conn.close()

if __name__ == '__main__':
    # 执行完整流程：从data.csv读取数据 -> 使用process_data.sql处理 -> 导出到result.csv
    print("=== 开始执行数据提取流程 ===")
    
    # 1. 删除旧数据库文件（如果存在）
    if os.path.exists("new_database.db"):
        os.remove("new_database.db")
        print("已删除旧数据库文件")
    
    # 2. 初始化数据库，创建表结构并清空表
    init_data_base()
    
    # 3. 导入CSV数据
    rows = import_csv_data("data.csv")
    
    # 4. 执行数据处理（仅提取信息，不再清空表）
    process_data()
    
    # 4. 导出结果到CSV文件
    if os.path.exists("result.csv"):
        os.remove("result.csv")
        print("已删除旧导出文件")
    if rows > 0:
        export_to_csv("result.csv")
    
    print("=== 流程执行完成 ===")
    